package vn.anhqn.englishsentensetranslator.utils;

import java.util.ArrayList;
import java.util.List;

import vn.anhqn.englishsentensetranslator.model.Category;
import vn.anhqn.englishsentensetranslator.model.Sentense;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class SqliteHelper extends SQLiteOpenHelper {

	private static final int DATABASE_VERSION = 1;
	private static final String DATABASE_NAME = "english_sentenses_lookup";

	// table name
	private static final String TABLE_SENTENSE = "english_to_your_lang";
	private static final String TABLE_CATEGORIES = "categories";
	// Key name
	private final String KEY_ID = "id";
	private final String KEY_FROM_LANG = "from_lang";
	private final String KEY_TO_LANG = "to_lang";
	private final String KEY_NAME = "name";
	private final String KEY_CATEGORY_ID = "category_id";

	public SqliteHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

		String CREATE_SENTENSES_TABLE = "CREATE TABLE " + TABLE_SENTENSE + "(" + KEY_ID
				+ " INTEGER PRIMARY KEY," + KEY_FROM_LANG + " TEXT," + KEY_TO_LANG + " TEXT," + KEY_CATEGORY_ID + " INTEGER )";

		String CREATE_CATEGORIES_TABLE = "CREATE TABLE " + TABLE_CATEGORIES + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME
				+ " TEXT )";

		db.execSQL(CREATE_SENTENSES_TABLE);
		db.execSQL(CREATE_CATEGORIES_TABLE);
		// create defaul category

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_CATEGORIES);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_SENTENSE);
		onCreate(db);
	}

	public ContentValues getSentenseContenValues(Sentense sentense) {
		ContentValues values = new ContentValues();
		values.put(KEY_FROM_LANG, sentense.getFromLang());
		values.put(KEY_TO_LANG, sentense.getToLang());
		values.put(KEY_CATEGORY_ID, sentense.getIdCategory());
		return values;
	}

	// add new sentense english to you language
	public long addSentense(Sentense sentense) {
		SQLiteDatabase db = this.getWritableDatabase();
		// Inserting Row
		long id = db.insert(TABLE_SENTENSE, null, getSentenseContenValues(sentense));
		db.close(); // Closing database connection
		return id;
	}


	// get sentense
	private Sentense getSentense(int id, String tableName) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(tableName, new String[] { KEY_ID, KEY_FROM_LANG, KEY_TO_LANG, KEY_CATEGORY_ID }, KEY_ID + "=?",
				new String[] { String.valueOf(id) }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();
		Sentense sentense = new Sentense(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getInt(3));
		// return contact
		return sentense;

	}

	// Getting sentense English to your language
	public Sentense getSentenseEtoY(int id) {
		return getSentense(id, TABLE_SENTENSE);
	}


	// get all category
	private List<Sentense> getAllSentenses(String tableName) {
		List<Sentense> sentenseList = new ArrayList<Sentense>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + tableName;
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Sentense sentense = new Sentense(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getInt(3));
				// Adding contact to list
				sentenseList.add(sentense);
			} while (cursor.moveToNext());
		}
		// return contact list
		return sentenseList;
	}

	// get all english to your language sentense
	public List<Sentense> getAllSentensesEtoY() {
		return getAllSentenses(TABLE_SENTENSE);
	}


	// Updating sentense
	private int updateSentense(Sentense sentense, String tableName) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_FROM_LANG, sentense.getFromLang());
		values.put(KEY_TO_LANG, sentense.getToLang());
		values.put(KEY_CATEGORY_ID, sentense.getIdCategory());
		// updating row
		return db.update(tableName, values, KEY_ID + " = ?", new String[] { String.valueOf(sentense.getId()) });
	}

	public int updateSentenseEtoY(Sentense sentense) {
		return updateSentense(sentense, TABLE_SENTENSE);
	}


	// Deleting sentense
	private void deleteSentense(Sentense sentense, String tableName) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(tableName, KEY_ID + " = ?", new String[] { String.valueOf(sentense.getId()) });
		db.close();
	}

	public void deleteSentenseEtoY(Sentense sentense) {
		deleteSentense(sentense, TABLE_SENTENSE);
	}


	// Deleting all categories
	public void deleteAllSentense(String tableName) {
		SQLiteDatabase db = this.getWritableDatabase();
		String deleteSQL = "DELETE FROM " + tableName;
		db.execSQL(deleteSQL);
	}

	public void deleteAllSentenseEtoY() {
		deleteAllSentense(TABLE_SENTENSE);
	}


	private void deleteSentenseByIdCat(int idCat, String tableName) {
		SQLiteDatabase db = this.getWritableDatabase();
		String deleteSQL = "DELETE FROM " + tableName + " WHERE " + KEY_CATEGORY_ID + " ="+String.valueOf(idCat);
		// String deleteSQL = "DELETE FROM " + TABLE_CATEGORIES;
		db.execSQL(deleteSQL);
	}
	public void deleteEtoYSentenseByIdCat(int idCat) {
		deleteSentenseByIdCat(idCat, TABLE_SENTENSE);
	}
	
	
	// ////////////////////////////////////////////////////////////////////////////////////
	// ///////////////////////////////////////////////////////////////////////////////////

	public long addCategory(Category category) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(KEY_NAME, category.getName());
		// Inserting Row
		long id = db.insert(TABLE_CATEGORIES, null, values);
		db.close(); // Closing database connection
		return id;
	}

	// get category
	public Category getCategory(int id) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(TABLE_CATEGORIES, new String[] { KEY_ID, KEY_NAME }, KEY_ID + "=?",
				new String[] { String.valueOf(id) }, null, null, null, null);
		if (cursor != null) {
			if (cursor.moveToFirst()) {
				Category category = new Category(Integer.parseInt(cursor.getString(0)), cursor.getString(1));
				return category;
			}
		}
		return null;

	}

	// Getting All categories
	public List<Category> getAllCategory() {
		List<Category> categoryList = new ArrayList<Category>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_CATEGORIES;
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Category category = new Category(cursor.getInt(0), cursor.getString(1));
				// Adding contact to list
				categoryList.add(category);
			} while (cursor.moveToNext());
		}
		// return contact list
		return categoryList;
	}

	// Updating categories
	public int updateCategory(Category category) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_NAME, category.getName());
		// updating row
		return db.update(TABLE_CATEGORIES, values, KEY_ID + " = ?", new String[] { String.valueOf(category.getId()) });
	}

	// Deleting categories
	public void deleteCategory(Category category) {
		if (category.getId() != 1) {
			SQLiteDatabase db = this.getWritableDatabase();
			db.delete(TABLE_CATEGORIES, KEY_ID + " = ?", new String[] { String.valueOf(category.getId()) });
			db.close();
		}
	}// Deleting all categories

	public void deleteAllCategory() {
		SQLiteDatabase db = this.getWritableDatabase();
		String deleteSQL = "DELETE FROM " + TABLE_CATEGORIES + " WHERE " + KEY_ID + " !=1";
		// String deleteSQL = "DELETE FROM " + TABLE_CATEGORIES;
		db.execSQL(deleteSQL);
	}
}
